SQL Tutorial Home

How to Round Timestamps

SQL Tutorial Home

A timestamp can have different levels of precision. It can represent just a day, or a day with hour and minutes, or even a day with hour, minutes, seconds and milliseconds. In the same way if we have a timestamp with full precision, we can trunc it by discarding some parts.

WITH Datetimes AS (
  SELECT DATETIME '2022-01-03 12:34:56' AS datetime UNION ALL
  SELECT DATETIME '2021-12-31 11:21:04'
)
SELECT
  Datetime                         as original_value,
  datetime_trunc(datetime, hour)   as trunc_hour,
  datetime_trunc(datetime, minute) as round_to_minute,
  datetime_trunc(datetime, day)    as round_to_day,
  datetime_trunc(datetime, month)  as round_to_month
FROM Datetimes
ORDER BY datetime;

`original_value`	`rounded_to_hour`	`rounded_to_minute`	`rounded_to_day`	`rounded_to_month`
`2022-01-03 12:34:56`	`2022-01-03 12:00:00`	`2022-01-03 12:34:00`	`2022-01-03 00:00:00`	`2022-01-01 00:00:00`
`2021-12-31 11:21:04`	`2021-12-31 11:00:00`	`2021-12-31 11:21:00`	`2021-12-31 00:00:00`	`2021-12-01 00:00:00`